package com.project.dao.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.project.dao.BorrowReturnInfoDAO;
import com.project.dto.BorrowBookRank;
import com.project.dto.ReaderBorrowRank;
import com.project.entity.BookInfo;
import com.project.entity.BorrowReturnInfo;
import com.project.entity.ReaderInfo;

/**
 * 
 * @author beryll@synnex.com
 * 
 */
@Repository("borrowReturnInfoDAO")
public class BorrowReturnInfoDAOImpl extends
		GenericDAOImpl<BorrowReturnInfo, Integer> implements
		BorrowReturnInfoDAO {
	// 分页
	@SuppressWarnings("unchecked")
	@Override
	public List<BorrowReturnInfo> getBorrowList(String bookName,
			String readerName, int pageSize, int pageNow, String... checks) {

		String hql1 = "select br.id, b.name, r.name, br.borrowedDate, br.shouldReturnDate, br.isReturn, br.returnDate,br.overDate "
				+ "from BorrowReturnInfo br,BookInfo b,ReaderInfo r where br.readerInfo.id = r.id and br.bookInfo.id=b.id ";
		String hql2 = "";
		String hql3 = " order by br.borrowedDate";
		String hql = "";
		List<Object[]> list = new ArrayList<Object[]>();
		int num = checks.length;
		if (num == 1) {
			if ("returned".equals(checks[0])) {
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and b.name like ? and br.isReturn=1";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and r.name like ? and br.isReturn=1";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and b.name like ? and r.name like ? and br.isReturn=1";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else {
					hql2 = " and br.isReturn=1";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				}
			} else if ("noReturn".equals(checks[0])) {
				// List<Object[]> list = new ArrayList<Object[]>();
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and b.name like ? and br.isReturn=0";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and r.name like ? and br.isReturn=0";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and b.name like ? and r.name like ? and br.isReturn=0";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else {
					hql2 = " and br.isReturn=0";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				}

			} else {
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and b.name like ?";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and r.name like ?";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and b.name like ? and r.name like ? ";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				} else {
					hql2 = " ";
					hql = hql1 + hql2 + hql3;
					list = getSession().createQuery(hql)
							.setFirstResult((pageNow - 1) * pageSize)
							.setMaxResults(pageSize).list();
				}
			}
		}

		if (num == 2) {
			if ((StringUtils.isNotBlank(bookName))
					&& (StringUtils.isBlank(readerName))) {
				hql2 = "  and b.name like ?";
				hql = hql1 + hql2 + hql3;
				list = getSession().createQuery(hql)
						.setString(0, "%" + bookName + "%")
						.setFirstResult((pageNow - 1) * pageSize)
						.setMaxResults(pageSize).list();
			} else if ((StringUtils.isBlank(bookName))
					&& (StringUtils.isNotBlank(readerName))) {
				hql2 = "  and r.name like ?";
				hql = hql1 + hql2 + hql3;
				list = getSession().createQuery(hql)
						.setString(0, "%" + readerName + "%")
						.setFirstResult((pageNow - 1) * pageSize)
						.setMaxResults(pageSize).list();
			} else if ((StringUtils.isNotBlank(bookName))
					&& (StringUtils.isNotBlank(readerName))) {
				hql2 = "  and b.name like ? and r.name like ?";
				hql = hql1 + hql2 + hql3;
				list = getSession().createQuery(hql)
						.setString(0, "%" + bookName + "%")
						.setString(1, "%" + readerName + "%")
						.setFirstResult((pageNow - 1) * pageSize)
						.setMaxResults(pageSize).list();
			} else {
				hql2 = " ";
				hql = hql1 + hql2 + hql3;
				list = getSession().createQuery(hql)
						.setFirstResult((pageNow - 1) * pageSize)
						.setMaxResults(pageSize).list();
			}
		}
		List<BorrowReturnInfo> brlist = new ArrayList<BorrowReturnInfo>();
		for (Object[] objects : list) {
			BorrowReturnInfo borrowreturnInfo = new BorrowReturnInfo();
			BookInfo bookInfo = new BookInfo();
			ReaderInfo readerInfo = new ReaderInfo();

			borrowreturnInfo.setId((Integer) objects[0]);

			bookInfo.setName(objects[1].toString());
			borrowreturnInfo.setBookInfo(bookInfo);

			readerInfo.setName(objects[2].toString());
			borrowreturnInfo.setReaderInfo(readerInfo);

			borrowreturnInfo.setBorrowedDate((Date) objects[3]);
			borrowreturnInfo.setShouldReturnDate((Date) objects[4]);
			borrowreturnInfo.setIsReturn((Integer) objects[5]);
			borrowreturnInfo.setReturnDate((Date) objects[6]);
			borrowreturnInfo.setOverDate((Integer) objects[7]);
			brlist.add(borrowreturnInfo);
		}
		return brlist;
	}

	@Override
	public Integer borrowedCount(String bookName, String readerName,
			String... checks) {
		String hql1 = "select count(*) from BorrowReturnInfo where 1=1 ";
		String hql2 = "";
		String hql = "";
		int totalCount = 0;
		int num = checks.length;
		if (num == 1) {
			if ("returned".equals(checks[0])) { //
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and bookInfo.name like ? and isReturn=1";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%").uniqueResult()
							.toString());
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and readerInfo.name like ? and isReturn=1";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.uniqueResult().toString());
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and bookInfo.name like ? and readerInfo.name like ? and isReturn=1";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.uniqueResult().toString());
				} else {
					hql2 = " and isReturn=1";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.uniqueResult().toString());
				}

			} else if ("noReturn".equals(checks[0])) {
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and bookInfo.name like ? and isReturn=0";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%").uniqueResult()
							.toString());
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and readerInfo.name like ? and isReturn=0";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.uniqueResult().toString());
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and bookInfo.name like ? and readerInfo.name like ? and isReturn=0";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.uniqueResult().toString());
				} else {
					hql2 = " and isReturn=0";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.uniqueResult().toString());
				}
			} else {
				if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isBlank(readerName))) {
					hql2 = "  and bookInfo.name like ?";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%").uniqueResult()
							.toString());
				} else if ((StringUtils.isBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and readerInfo.name like ?";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + readerName + "%")
							.uniqueResult().toString());
				} else if ((StringUtils.isNotBlank(bookName))
						&& (StringUtils.isNotBlank(readerName))) {
					hql2 = "  and bookInfo.name like ? and readerInfo.name like ?";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.setString(0, "%" + bookName + "%")
							.setString(1, "%" + readerName + "%")
							.uniqueResult().toString());
				} else {
					hql2 = " ";
					hql = hql1 + hql2;
					totalCount = Integer.parseInt(getSession().createQuery(hql)
							.uniqueResult().toString());
				}
			}
		}
		if (num == 2) {
			if ((StringUtils.isNotBlank(bookName))
					&& (StringUtils.isBlank(readerName))) {
				hql2 = "  and bookInfo.name like ?";
				hql = hql1 + hql2;
				totalCount = Integer.parseInt(getSession().createQuery(hql)
						.setString(0, "%" + bookName + "%").uniqueResult()
						.toString());
			} else if ((StringUtils.isBlank(bookName))
					&& (StringUtils.isNotBlank(readerName))) {
				hql2 = "  and readerInfo.name like ?";
				hql = hql1 + hql2;
				totalCount = Integer.parseInt(getSession().createQuery(hql)
						.setString(0, "%" + readerName + "%").uniqueResult()
						.toString());
			} else if ((StringUtils.isNotBlank(bookName))
					&& (StringUtils.isNotBlank(readerName))) {
				hql2 = "  and bookInfo.name like ? and readerInfo.name like ?";
				hql = hql1 + hql2;
				totalCount = Integer.parseInt(getSession().createQuery(hql)
						.setString(0, "%" + bookName + "%")
						.setString(1, "%" + readerName + "%").uniqueResult()
						.toString());
			} else {
				hql2 = " ";
				hql = hql1 + hql2;
				totalCount = Integer.parseInt(getSession().createQuery(hql)
						.uniqueResult().toString());
			}
		}
		return totalCount;
	}

	@Override
	public BorrowReturnInfo get(String bookname, String readername) {
		String hql = "from BorrowReturnInfo where bookInfo.name=? and readerInfo.name=? ";
		BorrowReturnInfo br = (BorrowReturnInfo) executeUniqueQuery(hql,
				bookname, readername);
		return br;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BorrowBookRank> borrowBookRank() {
		String hql = "select bookInfo.name, count(*) from BorrowReturnInfo group by bookInfo.name order by count(*) desc";
		List<Object[]> objects = (List<Object[]>) getSession().createQuery(hql)
				.setMaxResults(10).list();
		List<BorrowBookRank> list = new ArrayList<BorrowBookRank>();
		for (Object[] obj : objects) {
			BorrowBookRank borrowBookRank = new BorrowBookRank();
			borrowBookRank.setBookname(obj[0].toString());
			borrowBookRank.setBorrowCount(Integer.parseInt(obj[1].toString()));
			list.add(borrowBookRank);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ReaderBorrowRank> readerBorrowRank() {
		String hql = "select readerInfo.name, count(*) from BorrowReturnInfo group by readerInfo.name order by count(*) desc";
		List<Object[]> objects = (List<Object[]>) getSession().createQuery(hql)
				.setMaxResults(10).list();
		List<ReaderBorrowRank> list = new ArrayList<ReaderBorrowRank>();

		for (Object[] obj : objects) {
			ReaderBorrowRank readerBorrowRank = new ReaderBorrowRank();

			readerBorrowRank.setReaderName(obj[0].toString());
			readerBorrowRank.setCount(Integer.valueOf(obj[1].toString()));

			list.add(readerBorrowRank);
		}
		return list;
	}

	@Override
	public List<BorrowReturnInfo> get(String readerName) {
		String hql = "select br.id, b.name,r.name,br.borrowedDate"
				+ " from BorrowReturnInfo br,BookInfo b,ReaderInfo r where br.bookInfo.id = b.id and br.readerInfo.id=r.id and r.name = ? and br.isReturn=0";
		Query query = getSession().createQuery(hql);
		query.setString(0, readerName);
		List<Object[]> list = (List<Object[]>) query.list();
		List<BorrowReturnInfo> brlist = new ArrayList<BorrowReturnInfo>();
		for (Object[] objects : list) {
			BorrowReturnInfo brInfo = new BorrowReturnInfo();
			BookInfo bookInfo = new BookInfo();
			ReaderInfo readerInfo = new ReaderInfo();
			if (objects[0] != null) {
				brInfo.setId((Integer) objects[0]);
			}
			if (objects[1] != null) {
				bookInfo.setName(objects[1].toString());
				brInfo.setBookInfo(bookInfo);
			}
			if (objects[2] != null) {
				readerInfo.setName(objects[2].toString());
				brInfo.setReaderInfo(readerInfo);
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			// 小写的mm表示的是分钟
			if (objects[3] != null) {
				// Date date = new Date();
				// try {
				// System.out.println(objects[3].toString());
				// date = sdf.parse(objects[3].toString());
				// System.out.println(date);
				// } catch (ParseException e) {
				// e.printStackTrace();
				// }
				brInfo.setBorrowedDate((Date) objects[3]);
			}
			brlist.add(brInfo);
		}
		return brlist;
	}

	@Override
	public Integer borrowedAmount(String readerName) {
		String hql = "select count(*) from BorrowReturnInfo where readerInfo.name=? and isReturn=0 ";
		Object borrowedA = getSession().createQuery(hql)
				.setString(0, readerName).uniqueResult();
		int borrowedAmount = Integer.parseInt(borrowedA.toString());
		return borrowedAmount;
	}

}
